# -*- coding: utf-8 -*-
"""
Created on Wed Nov 15 17:11:10 2017

@author: leshuangjiang
"""

import xlwings as xw
import re
import os
import sqlite3 as sql
import time
import pandas as pd

conn = sql.connect(r'D:\价格批导\MYSQL\18Q3梭织.db')
cur = conn.cursor()
cfob_conn = sql.connect(r'D:\价格批导\MYSQL\cfob.db')
cfob_cur = cfob_conn.cursor()
cfob_acc = sql.connect(r"D:\价格批导\MYSQL\cfobAcc.db")
cfobacc_cur = cfob_acc.cursor()


def file_lst():
    path = r'D:\文档\表格\IE科\18Q4'
    path_lst = [os.path.join(path,i) for i in os.listdir(path)]
    return path_lst

def getLocation():
    D_col = xb.sheets[0].range('d1:d100').value
    init_first = D_col.index('面料编号')
    middle = D_col.index('辅料编号')
    last = D_col.index('议价人')
    return init_first, middle, last

def get_one_acc(kh):
    option = []
    select_kh = ('''select * from acce where acce.款号='{}' order by acce.款号'''.format(kh))
    cur.execute(select_kh)
    res = cur.fetchall()
    if res == []:
        print('此款号辅料数据还未导入')
    else:
        for i, v in enumerate(res):
            print(v[2], v[6])
            option.append([v[1], v[2], v[6]])
            xb.sheets[0].range('l'+str(i+35)).value = [v[2], v[6]]
    return option

def get_one_mat(kh):
    option = []
    select_kh = ('''select * from mate where mate.款号='{}' order by mate.款号'''.format(kh))
    cur.execute(select_kh)
    res = cur.fetchall()
    if res == []:
        print('此款号面料数据还未导入')
    else:
        for i, v in enumerate(res):
            print(v[1], v[8])
            option.append([v[1], v[2], v[8]])
#            xb.sheets[0].range('p'+str(i+2)).value = [v[1], v[8]]
    return option

def get_one_cfob(bh):
    a = []
    option = []
    for i in bh:
        select_bh = ('''select * from data where data.编号='{}' order by data.编号'''.format(i))
        cfob_cur.execute(select_bh)
        res = cfob_cur.fetchall()
        if res == []:
            print('此款CFOB面料数据还未导入')
        else:
            a.extend(res)
    for i, v in enumerate(a):
        print(int(v[0]), v[5])
        option.append([v[0], v[1], v[5]])
#        xb.sheets[0].range('n'+str(i+2)).value = [v[0], v[5]]
    return option

def get_acc_cfob(bh):
    a = []
    option = []
    for i in bh:
        select_bh = ('''select * from data where data.编号='{}' order by data.编号'''.format(i))
        cfobacc_cur.execute(select_bh)
        res = cfobacc_cur.fetchall()
        if res == []:
            print('此款CFOB辅料数据还未导入')
        else:
            a.extend(res)
    for i, v in enumerate(a):
        print(int(v[0]), v[1])
        option.append([v[0],v[1]])
    df_acc = pd.DataFrame(option)
    try:
        nl_acc = df_acc.loc[~df_acc.duplicated(),:]
    except ValueError as e:
        nl_acc = df_acc
    xb.sheets[0].range('o13').value = nl_acc.values
    return 'ok'

    return option

def regulardata(a, b):
    regular_data.extend(a)
    regular_data.extend(b)
#    regular_data.extend(c)
    df = pd.DataFrame(regular_data)
#        df[0].apply(lambda x: x.split('.')[0])
    nl = df.loc[~df.duplicated(),:]
    xb.sheets[0].range('l9').value = nl.values
    return 'ok'

app = xw.App(visible=True, add_book=False)
app.display_alerts = True
app.screen_updating = True

pl = file_lst()
for x in pl:
    regular_data = []
    try:
        name_kuanhao = re.findall(r'\\(\d{10}\d)', x[16:])[0]
        xb = app.books.open(x)
        xb.sheets[0].activate()
        print(name_kuanhao,'正在写入……')
        kh = int(name_kuanhao)
        a, b, c = getLocation()
        forbh = [i for i in xb.sheets[0].range('d'+str(a+2)+':'+'d'+str(b-1)).value if i != None]
        allbh = [i for i in xb.sheets[0].range('d'+str(a+2)+':'+'d'+str(c-5)).value if i != None]
        bh = [str(int(i)) if isinstance(i, float) else i for i in forbh]
        accbh = [str(int(i)) if isinstance(i, float) else i for i in allbh]
        a, b= [get_one_cfob(bh), get_one_mat(kh)]
        regulardata(a, b)
        get_one_acc(kh)
        get_acc_cfob(accbh)
    #    a, b, c = getLocation()
    #    one_num(a+1, b-1)
    #    one_num(b+1, c-2)
        app.screen_updating = True
        xb.save('d:\\price\\' + name_kuanhao +'+_+-.xlsx')
        print(name_kuanhao, '完成了\n')
        xb.close()
        time.sleep(0.001)
    except IndexError as e:
        pass
    finally:
        pass
print('所有的都已经写入，请检查！')
app.quit()
xw.App.kill(app)
cfob_conn.close()
conn.close()
cfob_acc.close()
#os.system('taskkill /F /IM EXCEL.EXE')